#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80.8090 --execute "


insert into hive.edu_dws.hcx_dws_cus_day_cnt
with temp1 as (select

                      substring(date_format(create_date_time, '%Y-%m-%d %H:%i:%s'), 1, 10) as create_date_time,
                      customer_id,
                      origin_type,
                      clue_state,
                      cus_area,
                      sub_name,
                      sch_name,
                      origin_channel,
                      department_name,
                     row_number() over(partition by customer_id) as all_rn,
                     row_number() over(partition by customer_id,cus_area) as area_rn,
                     row_number() over(partition by customer_id,sub_name) as sub_rn,
                     row_number() over(partition by customer_id,sch_name) as sch_rn,
                     row_number() over(partition by customer_id,origin_channel) as channel_rn,
                     row_number() over(partition by customer_id,department_name) as dep_rn

 from hive.edu_dwb.hcx_dwb_customer_relationship_wide)

select
       create_date_time,
       origin_type,
       clue_state,
       cus_area,
       sub_name,
       sch_name,
       origin_channel,
       department_name,
       case
           when grouping(cus_area) = 0 then 'area'
           when grouping(sub_name) = 0 then 'subject'
           when grouping(sch_name) = 0 then 'school'
           when grouping(origin_channel) = 0 then 'fromwhere'
           when grouping(department_name) = 0 then 'chatcenter'
           when grouping(clue_state) = 0 then 'all' end
           as group_type,


      cast(case
           when grouping(cus_area) = 0 then sum(if(area_rn = 1 and cus_area is not null,1,0))
           when grouping(sub_name) = 0 then sum(if(sub_rn = 1 and sub_name is not null,1,0))
           when grouping(sch_name) = 0 then sum(if(sch_rn = 1 and sch_name is not null,1,0))
           when grouping(origin_channel) = 0 then sum(if(channel_rn = 1 and origin_channel is not null,1,0))
           when grouping(department_name) = 0 then sum(if(dep_rn = 1 and department_name is not null,1,0))
           when grouping(clue_state) = 0 then sum(if(all_rn = 1 and clue_state is not null,1,0)) end as int )
           as cus_count
from temp1
group by
    grouping sets( (create_date_time, origin_type, clue_state),
                  (create_date_time, origin_type, clue_state,cus_area),
                  (create_date_time, origin_type, clue_state,sub_name),
                  (create_date_time, origin_type, clue_state,sch_name),
                  (create_date_time, origin_type, clue_state,origin_channel),
                  (create_date_time, origin_type, clue_state,department_name)
    );

insert into  hive.edu_dws.hcx_dws_clu_day_cnt
WITH temp2 AS (
    SELECT id,
           SUBSTRING(DATE_FORMAT(create_date_time, '%Y-%m-%d %H:%i:%s'), 1, 10) AS create_date_time_day,
           SUBSTRING(DATE_FORMAT(create_date_time, '%Y-%m-%d %H:%i:%s'), 1, 13) AS create_date_time_hour,
           origin_type,
           clue_state,
           appeal_status,
           ROW_NUMBER() OVER(PARTITION BY id ) AS all_rn
    FROM hive.edu_dwb.hcx_dwb_customer_clue_wide
),

daily_summary as ( SELECT create_date_time_day,
           origin_type,
           clue_state,
           sum(CASE WHEN  all_rn = 1 THEN 1 ELSE 0 END) as day_sum_count,
           SUM(CASE WHEN appeal_status = '无效' AND all_rn = 1 THEN 1 ELSE 0 END) AS cnt_day_sub

    FROM temp2
    GROUP BY create_date_time_day, origin_type, clue_state),

 hour_summary as (   SELECT create_date_time_hour,
           origin_type,
           clue_state,
           sum(CASE WHEN  all_rn = 1 THEN 1 ELSE 0 END) as cnt_hour,
           SUM(CASE WHEN appeal_status = '无效' AND all_rn = 1 THEN 1 ELSE 0 END) AS cnt_hour_sub,

           sum(CASE WHEN  all_rn = 1 THEN 1 ELSE 0 END) -
           SUM(CASE WHEN appeal_status = '无效' AND all_rn = 1 THEN 1 ELSE 0 END)   as cnt_hour_good
    FROM temp2
    GROUP BY create_date_time_hour, origin_type, clue_state)



SELECT ds.create_date_time_day as create_date_time_day,
       hs.create_date_time_hour as create_date_time_hour,
       ds.origin_type as origin_type,
       ds.clue_state as clue_state,
--        ds.day_sum_count,
--        ds.cnt_day_sub,
       ds.day_sum_count- ds.cnt_day_sub as cnt_day,
       hs.cnt_hour as cnt_hour,
--        hs.cnt_hour_sub as cnt_hour_sub,
         cnt_hour_good,
       (CAST(hs.cnt_hour_good AS DECIMAL(38,3))*1.0 / NULLIF(hs.cnt_hour, 0)) AS change_rate
FROM daily_summary ds
LEFT JOIN hour_summary hs
ON ds.create_date_time_day = SUBSTRING(hs.create_date_time_hour, 1, 10)
AND ds.origin_type = hs.origin_type
AND ds.clue_state = hs.clue_state
ORDER BY ds.create_date_time_day, hs.create_date_time_hour






--  select
--         create_date_time,
--         create_date_time_hour,
--         origin_type,
--         clue_state,
--         appeal_status,
--          case
--             when grouping(create_date_time) = 0 then 'day_available'
--             when grouping(create_date_time_hour) = 0 then 'hour_available'
--           end
--          as group_type,
--         SUM(CASE WHEN GROUPING(create_date_time) = 0 AND appeal_status = '有效' AND all_rn = 1
--             THEN 1 ELSE 0 END) AS cnt_day,
--
--         SUM(CASE WHEN GROUPING(create_date_time_hour) = 0 AND all_rn = 1
--             THEN 1 ELSE 0 END) AS cnt_hour,
--
--         SUM(CASE WHEN GROUPING(create_date_time_hour) = 0 AND appeal_status = '有效' AND all_rn = 1
--             THEN 1 ELSE 0 END) AS cnt_hour_good
--
--  from temp2
--  group by
--  grouping sets (
--  (create_date_time, origin_type, clue_state, appeal_status),
--  (create_date_time_hour,origin_type, clue_state, appeal_status)
--      );



"